package com.studentbook.dao;

import com.studentbook.bean.StudentContact;
import com.studentbook.util.DBUtil;
import com.studentbook.util.FileUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 学生联系人数据访问层
 */
public class StudentContactDao {

    /**
     * 添加学生联系人
     * @param contact 学生联系人对象
     * @return 影响的行数
     */
    public int add(StudentContact contact) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int result = 0;

        try {
            conn = DBUtil.getConnection();
            String sql =  "INSERT INTO student_contact(student_id, name, grade, contact_info) " +
                    "VALUES (?, ?, ?, ?) " +
                    "ON DUPLICATE KEY UPDATE " +
                    "name = VALUES(name), " +
                    "grade = VALUES(grade), " +
                    "contact_info = VALUES(contact_info)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, contact.getStudentId());
            pstmt.setString(2, contact.getName());
            pstmt.setString(3, contact.getGrade());
            pstmt.setString(4, contact.getContactInfo());

            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt);
        }

        return result;
    }

    /**
     * 根据ID删除学生联系人
     * @param id 联系人ID
     * @return 影响的行数
     */
    public int deleteById(int id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int result = 0;

        try {
            conn = DBUtil.getConnection();
            String sql = "DELETE FROM student_contact WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);

            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt);
        }

        return result;
    }

    /**
     * 根据学号删除学生联系人
     * @param studentId 学号
     * @return 影响的行数
     */
    public int deleteByStudentId(String studentId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int result = 0;

        try {
            conn = DBUtil.getConnection();
            String sql = "DELETE FROM student_contact WHERE student_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, studentId);

            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt);
        }

        return result;
    }

    /**
     * 更新学生联系人信息
     * @param contact 学生联系人对象
     * @return 影响的行数
     */
    public int update(StudentContact contact) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int result = 0;

        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE student_contact SET name = ?, grade = ?, contact_info = ? WHERE student_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, contact.getName());
            pstmt.setString(2, contact.getGrade());
            pstmt.setString(3, contact.getContactInfo());
            pstmt.setString(4, contact.getStudentId());

            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt);
        }

        return result;
    }

    /**
     * 根据ID查询学生联系人
     * @param id 联系人ID
     * @return 学生联系人对象
     */
    public StudentContact findById(int id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        StudentContact contact = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM student_contact WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);

            rs = pstmt.executeQuery();
            if (rs.next()) {
                contact = new StudentContact();
                contact.setId(rs.getInt("id"));
                contact.setStudentId(rs.getString("student_id"));
                contact.setName(rs.getString("name"));
                contact.setGrade(rs.getString("grade"));
                contact.setContactInfo(rs.getString("contact_info"));
                contact.setCreateTime(rs.getTimestamp("create_time"));
                contact.setUpdateTime(rs.getTimestamp("update_time"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return contact;
    }

    /**
     * 根据学号查询学生联系人
     * @param studentId 学号
     * @return 学生联系人对象
     */
    public StudentContact findByStudentId(String studentId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        StudentContact contact = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM student_contact WHERE student_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, studentId);

            rs = pstmt.executeQuery();
            if (rs.next()) {
                contact = new StudentContact();
                contact.setId(rs.getInt("id"));
                contact.setStudentId(rs.getString("student_id"));
                contact.setName(rs.getString("name"));
                contact.setGrade(rs.getString("grade"));
                contact.setContactInfo(rs.getString("contact_info"));
                contact.setCreateTime(rs.getTimestamp("create_time"));
                contact.setUpdateTime(rs.getTimestamp("update_time"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return contact;
    }

    /**
     * 根据姓名查询学生联系人
     * @param name 姓名
     * @return 学生联系人列表
     */
    public List<StudentContact> findByName(String name) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<StudentContact> contactList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM student_contact WHERE name LIKE ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + name + "%");

            rs = pstmt.executeQuery();
            while (rs.next()) {
                StudentContact contact = new StudentContact();
                contact.setId(rs.getInt("id"));
                contact.setStudentId(rs.getString("student_id"));
                contact.setName(rs.getString("name"));
                contact.setGrade(rs.getString("grade"));
                contact.setContactInfo(rs.getString("contact_info"));
                contact.setCreateTime(rs.getTimestamp("create_time"));
                contact.setUpdateTime(rs.getTimestamp("update_time"));
                contactList.add(contact);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return contactList;
    }

    /**
     * 查询所有学生联系人
     * @return 学生联系人列表
     */
    public List<StudentContact> findAll() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<StudentContact> contactList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM student_contact ORDER BY id ASC";
            pstmt = conn.prepareStatement(sql);

            rs = pstmt.executeQuery();
            while (rs.next()) {
                StudentContact contact = new StudentContact();
                contact.setId(rs.getInt("id"));
                contact.setStudentId(rs.getString("student_id"));
                contact.setName(rs.getString("name"));
                contact.setGrade(rs.getString("grade"));
                contact.setContactInfo(rs.getString("contact_info"));
                contact.setCreateTime(rs.getTimestamp("create_time"));
                contact.setUpdateTime(rs.getTimestamp("update_time"));
                contactList.add(contact);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return contactList;
    }
    public boolean importContactsFromFileSyncWithDB(String filePath) {
        List<StudentContact> contacts = FileUtil.loadContactsFromFile(filePath);
        StudentContactDao dao = new StudentContactDao();

        for (StudentContact contact : contacts) {
            StudentContact existContact = dao.findByStudentId(contact.getStudentId());
            if (existContact == null) {
                dao.add(contact);
            } else {
                contact.setId(existContact.getId());
                dao.update(contact);
            }
        }

        return true;
    }
}
